// <copyright file="DtUtilWrapper.cs" company="Bill Fellows">
// Copyright (c) But not really, use it as you see fit. All rights reserved.
// </copyright>
namespace SSISDeploy.Components
{
    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.IO;
    using System.Diagnostics;
    using System.Text.RegularExpressions;

    using Microsoft.Win32;

    /// <summary>
    /// A simple struct to allow me to overcome the limitation of only returning item.
    /// </summary>
    public struct ProcessResults
    {
        /// <summary>
        /// The text sent to the standard output
        /// </summary>
        public string StandardOutput;

        /// <summary>
        /// The text sent to standard error output
        /// </summary>
        public string StandardError;

        /// <summary>
        /// The process return code.  0 in happy path, non-zero elsewhere
        /// </summary>
        public int ReturnCode;
    }

    /// <summary>
    /// This class will provide a very thin wrapper around the methods for the dtutil
    /// executible.  Reason behind this being I may find that there are either
    /// stored procs in SQL Server or SMO objects I can call to get the same results
    /// as beating against the dtutil thingamabobber and if that day comes, I should
    /// be able to swap out the underpinnings.  I hope this doesn't make it too
    /// "enterprisey".
    /// </summary>
    public class DtUtilWrapper
    {
        /// <summary>
        /// The fully qualified path to the dtutil executable
        /// </summary>
        private string dtUtilPath;

        /// <summary>
        /// A template for the thread that will be our minion
        /// </summary>
        private System.Diagnostics.ProcessStartInfo processStartInfo;

        /// <summary>
        /// When true, we will only simulate via standard out all the actions we would take.
        /// However, this needs to account for non-destructive actions like Directory and exists
        /// operations.
        /// </summary>
        private bool simulate;

        /// <summary>
        /// Promoted to class level variable as it appears to improve testing performance
        /// </summary>
        private ProcessResults results;

        /// <summary>
        /// The default constructor.  
        /// TODO:  
        /// For now, it has a hardcoded path but it might be
        /// better served by pulling information from the registry or allowing it as an
        /// application option/command line argument.  Need to look at 64 bit to see
        /// how it is set up in registries.
        /// </summary>
        public DtUtilWrapper()
        {
            this.dtUtilPath = string.Format("{0}{1}", this.GetSQLServerDirectory(), @"Binn\dtutil.exe");
            this.simulate = false;
        }

        /// <summary>
        /// Gets or sets a value indicating whether it should actually perform actions or
        /// simply dump all the actions it would have taken to the console.
        /// </summary>
        public bool SimulateOnly
        {
            get { return this.simulate; }
            set { this.simulate = value; }
        }

        /// <summary>
        /// Create a folder on SQL server.
        /// TODO:  Globally, figure out what will be the most appropriate way of handling
        /// and reporting errors.
        /// </summary>
        /// <param name="server">A SQL Server name</param>
        /// <param name="parentFolderPath">The path to the parent folder</param>
        /// <param name="newFolderName">The name of the folder to be created</param>
        /// <returns>true/false based on the successful creation of a folder</returns>
        public bool CreateFolder(string server, string parentFolderPath, string newFolderName)
        {
            bool folderCreated = false;
            string parameters = string.Empty;
            parameters = string.Format(@" /SourceS {0} /FCreate ""SQL;{1};{2}""", server, parentFolderPath, newFolderName);
            
            // Console.WriteLine(parameters);
            // Console.WriteLine(string.Format("Creating folder {0} as a child of {1} on server {2}", newFolderName, parentFolderPath, server));
            folderCreated = this.RunProcess(parameters);
            return folderCreated;
        }

        /// <summary>
        /// Create all the folders given a path
        /// </summary>
        /// <param name="server">A SQL Server servername</param>
        /// <param name="packagePath">A fully qualified SQL Server path</param>
        /// <returns>true if it was able to create all the folders</returns>
        public bool CreateFoldersRecursive(string server, string packagePath)
        {
            bool results = false;
            string parentFolder = string.Empty;
            string currentFolder = string.Empty;
            parentFolder = this.GetParentFolder(packagePath);
            currentFolder = this.GetCurrentFolder(packagePath);
            if (!this.FolderExists(server, packagePath))
            {
                this.CreateFoldersRecursive(server, parentFolder);
                results = this.CreateFolder(server, parentFolder, currentFolder);
            }
            else
            {
                results = true;
            }

            return results;
        }

        /// <summary>
        /// Delete a folder on SQL Server
        /// </summary>
        /// <param name="server">A SQL Server server</param>
        /// <param name="parentFolderPath">The path to the parent folder</param>
        /// <param name="folderName">The name of the folder to be removed</param>
        /// <returns>true/false based on whether we were able to purge the folder</returns>
        public bool DeleteFolder(string server, string parentFolderPath, string folderName)
        {
            bool folderDeleted = false;
            string parameters = string.Empty;
            parameters = string.Format(@" /SourceS {0} /FDelete ""SQL;{1};{2}""", server, parentFolderPath, folderName);

            // Console.WriteLine(string.Format("Deleting folder {0} as a child of {1} on server {2}", folderName, parentFolderPath, server));
            folderDeleted = this.RunProcess(parameters);
            return folderDeleted;
        }

        /// <summary>
        /// Remove all the elements and sub-elements from a SQL Server SSIS deployment
        /// </summary>
        /// <param name="server">A SQL Server servername</param>
        /// <param name="parentFolderPath">The root location to begin pruning</param>
        /// <returns>true/false based on the successful removal of content</returns>
        public bool DeleteFolderRecursive(string server, string parentFolderPath)
        {
            bool folderDeleted = false;
            string parameters = string.Empty;
            foreach (PackageFolder item in this.GetDirectory(server, parentFolderPath))
            {
                // Console.WriteLine(item);
                if (item.IsFolder)
                {
                    this.DeleteFolderRecursive(server, string.Format(@"{0}\{1}", parentFolderPath, item.Name));
                    folderDeleted = this.DeleteFolder(server, parentFolderPath, item.Name);
                }
                else if (item.IsPackage)
                {
                    folderDeleted = this.DeletePackage(server, parentFolderPath, item.Name);
                }
            }

            return folderDeleted;
        }

        /// <summary>
        /// Remove a DTSX/SSIS package from SQL server
        /// </summary>
        /// <param name="server">A SQL Server servername</param>
        /// <param name="parentFolderPath">The root location to find a package</param>
        /// <param name="packageName">The name of the SSIS package as it is defined in SQL Server (sans-extension)</param>
        /// <returns>true/false based on the successful removal of a package</returns>
        public bool DeletePackage(string server, string parentFolderPath, string packageName)
        {
            bool packageDeleted = false;
            string parameters = string.Empty;
            parameters = string.Format(@" /SourceS {0} /Delete /SQL ""{1}\\{2}""", server, parentFolderPath, packageName);
            
            // Console.WriteLine(parameters);
            packageDeleted = this.RunProcess(parameters);
            return packageDeleted;
        }

        /// <summary>
        /// Transfer an SSIS package to the SQL Server
        /// </summary>
        /// <param name="server">A SQL Server servername</param>
        /// <param name="pathToDtsx">The location on disk (local or UNC) where the file may be found</param>
        /// <param name="packagePathAndName">The fully qualified location of where the package should reside in SQL Server as well as its name</param>
        /// <returns>true/false based on the successful copy of a package</returns>
        public bool CopyPackage(string server, string pathToDtsx, string packagePathAndName)
        {
            bool packageLoaded = false;
            string parameters = string.Empty;
            parameters = string.Format(@" /DestS {0} /File ""{1}"" /Copy ""SQL;{2}""", server, pathToDtsx, packagePathAndName);
            
            // Console.WriteLine(parameters);
            packageLoaded = this.RunProcess(parameters);
            return packageLoaded;
        }

        /// <summary>
        /// Test whether an item exists in SQL Server
        /// </summary>
        /// <param name="server">A SQL Server servername</param>
        /// <param name="packagePathAndName">The fully qualified path and packagename in SQL Server</param>
        /// <returns>true/false based on whether the item exists within SQL Server</returns>
        public bool PackageExists(string server, string packagePathAndName)
        {
            bool packageExists = false;
            string parameters = string.Empty;
            parameters = string.Format(@" /SourceS {0} /Exists /SQL ""{1}""", server, packagePathAndName);

            // Console.WriteLine(parameters);
            packageExists = this.RunProcess(parameters);
            return packageExists;
        }

        /// <summary>
        /// Test whether a folder exists in SQL Server
        /// </summary>
        /// <param name="server">A SQL Server servername</param>
        /// <param name="packagePath">The fully qualified path in SQL Server</param>
        /// <returns>true/false based on whether the folder exists within SQL Server</returns>
        public bool FolderExists(string server, string packagePath)
        {
            bool packageExists = false;
            string parameters = string.Empty;
            parameters = string.Format(@" /SourceS {0} /FExists ""SQL;{1}""", server, packagePath);
            
            // Console.WriteLine(parameters);
            // ProcessResults results;
            // results = this.RunProcessWithOutput(parameters);
            this.RunProcessWithOutput(parameters);
            packageExists = this.results.ReturnCode == 0;

            return packageExists;
        }

        /// <summary>
        /// Test whether a folder exists in SQL Server
        /// </summary>
        /// <param name="server">A SQL Server servername</param>
        /// <param name="parentFolderPath">The path to a folder's parent</param>
        /// <param name="folderName">The name of folder</param>
        /// <returns>true/false based on whether the folder exists</returns>
        public bool FolderExists(string server, string parentFolderPath, string folderName)
        {
            // Should I push this into the invoking method's logic?
            if (!(parentFolderPath.EndsWith(@"\\") || folderName.StartsWith(@"\\")))
            {
                parentFolderPath = parentFolderPath + @"\\";
            }

            // a path should look like \something\some\something
            return this.FolderExists(server, parentFolderPath + folderName);
        }

        /// <summary>
        /// Rename a folder
        /// </summary>
        /// <param name="server">The server to attempt the rename operation against</param>
        /// <param name="parentPath">The fully qualified path to the parent folder</param>
        /// <param name="oldFolder">The item to be renamed</param>
        /// <param name="newFolder">The new name for the item</param>
        /// <returns>A boolean indicating success/failure</returns>
        public bool RenameFolder(string server, string parentPath, string oldFolder, string newFolder)
        {
            bool rename = false;
            string parameters = string.Empty;
            parameters = string.Format(@" /SourceS {0} /FRename ""SQL;{1};{2};{3}", server, parentPath, oldFolder, newFolder);

            this.RunProcessWithOutput(parameters);
            rename = this.results.ReturnCode == 0;
            return rename;
        }

        /// <summary>
        /// Retrieve the contents of a folder within SQL Server
        /// </summary>
        /// <param name="server">A SQL Server servername</param>
        /// <param name="parentFolderPath">The fully qualified path in SQL Server.  e.g. \ or \folder1\folder2\folder3</param>
        /// <returns>A list of PackageFolder objects containing the contents of parentFolderPath</returns>
        public List<PackageFolder> GetDirectory(string server, string parentFolderPath)
        {
            List<PackageFolder> contents = null;
            string parameters = string.Empty;
            parameters = string.Format(@" /SourceS {0} /FDirectory ""SQL;{1}""", server, parentFolderPath);

            contents = this.ParsePackagesAndFolders(this.RunProcessWithOutput(parameters).StandardOutput);
            return contents;
        }

        /// <summary>
        /// Run the process using the supplied parameter list
        /// </summary>
        /// <param name="parameters">The arguments to be passed to the process</param>
        /// <returns>true/false based on whether it ran well</returns>
        protected bool RunProcess(string parameters)
        {
            return this.RunProcessWithOutput(parameters).ReturnCode == 0;
        }

        /// <summary>
        /// Invoke our process with the given parameters and return a tuple of stdout, stderr and the return code.
        /// </summary>
        /// <param name="parameters">The parameters that should be passed to the process</param>
        /// <returns>A struct containing the output, error output and return code from the process</returns>
        protected ProcessResults RunProcessWithOutput(string parameters)
        {
            // ProcessResults results;
            this.results = new ProcessResults();

            if (this.simulate)
            {
                Console.WriteLine(string.Format(@"""{0}""{1}", this.dtUtilPath, parameters));
            }

            if (!this.simulate || (parameters.Contains("Exists") || parameters.Contains("FDirectory")))
            {
                this.processStartInfo = new ProcessStartInfo(this.dtUtilPath, parameters);
                this.processStartInfo.RedirectStandardError = true;
                this.processStartInfo.RedirectStandardOutput = true;
                this.processStartInfo.WindowStyle = ProcessWindowStyle.Hidden;
                this.processStartInfo.UseShellExecute = false;
                System.Diagnostics.Process process = null;

                // Perhaps it would be better to set this to 0 so that it runs to completion
                int waitInMilliSeconds = 1 * 1000;

                process = System.Diagnostics.Process.Start(this.processStartInfo);
                System.IO.StreamReader standardError = process.StandardError;
                System.IO.StreamReader standardOutput = process.StandardOutput;
                process.WaitForExit(waitInMilliSeconds);
                if (process.HasExited)
                {
                    // Did it do well?
                    this.results.StandardOutput = standardOutput.ReadToEnd();
                    this.results.StandardError = standardError.ReadToEnd();
                    this.results.ReturnCode = process.ExitCode;
                }
            }

            // 20080706 bfellows
            // If it didn't do well, echo the command we issued as well
            // as the stdout/err
            // This will go away or be handled more gracefully in the future
            // but for now, let's at least indicate that something went awry
            if (this.results.ReturnCode != 0)
            {
                Console.WriteLine(string.Format(@"Command Attempted:  ""{0}""{1}", this.dtUtilPath, parameters));
                Console.WriteLine(this.results.StandardOutput);
                Console.WriteLine(this.results.StandardError);
            }
            
            return this.results;
        }

        /// <summary>
        /// Extract the useful information from the provided message.  This assumes message is
        /// the output of the directory command. 
        /// <example>Microsoft (R) SQL Server SSIS Package Utilities
        /// Version 9.00.1399.06 for 32-bit
        /// Copyright (C) Microsoft Corp 1984-2004. All rights reserved.
        /// 
        /// Directory of \\
        /// 
        /// 6/19/2008  9:25:08 PM              4577 bfellows (v1.0.1)
        /// <Folder/>                Maintenance Plans
        /// <Folder/>                SID
        /// <Folder/>                Foo
        /// <Folder/>                Food
        /// <Folder/>                bfellows
        /// <Folder/>                recursive0
        /// There were 1 package(s) and 6 folder(s) found.
        /// </example>
        /// </summary>
        /// <param name="message">The standard output listing packages and folders</param>
        /// <returns>A list of structs indicating whether it's a package or a folder</returns>
        private List<PackageFolder> ParsePackagesAndFolders(string message)
        {
            List<PackageFolder> content = null;
            PackageFolder packageFolder = null;
            Regex folderNameExpression = null;
            Regex packageNameExpression = null;
            content = new List<PackageFolder>();

            // This was not the corrent regular expression as folders could have spaces in them
            folderNameExpression = new Regex(@"^<Folder>\s*(?<FolderName>.*)", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.ExplicitCapture);

            // Package names can be anything as long as it doesn't contain: / \ : [ ] . = 
            packageNameExpression = new Regex(
                @"
                (?<date>^\d[\d/]*\s\s[\d:\s]*\w\w)\s*
                (?<PackageSize>\d*)\s*
                (?<PackageName>\w*)\s*\(
                (?<PackageVersion>v[\d.]*)\)",
                RegexOptions.IgnoreCase | RegexOptions.IgnorePatternWhitespace | RegexOptions.Multiline | RegexOptions.ExplicitCapture);

            Match packageMatch = null;
            Match folderMatch = null;
            string name = string.Empty;
            string version = string.Empty;
            string createDate = string.Empty;
            
            foreach (string line in message.Split('\n'))
            {
                packageMatch = packageNameExpression.Match(line.Replace('\r', ' '));
                folderMatch = folderNameExpression.Match(line.Replace('\r', ' '));
                version = string.Empty;
                createDate = string.Empty;
                if (packageMatch.Success | folderMatch.Success)
                {
                    packageFolder = new PackageFolder();
                    packageFolder.IsPackage = packageMatch.Success;
                    packageFolder.IsFolder = folderMatch.Success;

                    if (packageMatch.Success)
                    {
                        createDate = packageMatch.Groups[1].Value;
                        name = packageMatch.Groups[3].Value;
                        version = packageMatch.Groups[4].Value;
                        packageFolder.CreateDate = createDate;
                        packageFolder.Version = version;
                    }
                    else if (folderMatch.Success)
                    {
                        name = folderMatch.Groups[1].Value;
                    }

                    packageFolder.Name = name;
                    content.Add(packageFolder);
                }
            }

            return content;
        }

        /// <summary>
        /// A good idea that didn't pan out.  This was going to build a full tree instead of just
        /// one level.  Couldn't come up with the appropriate data structure to express the information.
        /// Held here in case I come back to it, after designing a GenericTree<T/> class 
        /// </summary>
        /// <param name="message">The results of appending the S parameter to the directory inquiry</param>
        /// <returns>A list of packages and folders</returns>
        private List<PackageFolder> ParsePackagesAndFoldersRecursive(string message)
        {
            // List<string> output = null;
            // Regex currentDirectoryExpression = null;
            Regex folderListExpression = null;
            
            // Regex packageNameExpression = null;
            string folderList = string.Empty;

            folderList = @"
                ^<Folder>\s*(?<FolderName>\w*) | # Grab anything that starts with a name
                (?<date>^\d[\d/]*\s\s[\d:\s]*\w\w)\s*
                (?<PackageSize>\d*)\s*
                (?<PackageName>\w*)\s*\(
                (?<PackageVersion>v[\d.]*)\)
                ";

            folderListExpression = new Regex(folderList, RegexOptions.IgnorePatternWhitespace | RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.ExplicitCapture);

            // Console.WriteLine(folderListExpression.Replace(message, evaluator));
            // Console.WriteLine(folderListExpression.Match(message).Value);
            Match matches = null;
            matches = folderListExpression.Match(message);

            int folderPosition = 0;
            int packagePosition = 0;
            folderPosition = folderListExpression.GroupNumberFromName("Folder");
            packagePosition = folderListExpression.GroupNumberFromName("PackageName");

            foreach (Match m in folderListExpression.Matches(message))
            {
                // Console.WriteLine(string.Format("Using group number to get folder name: ", m.Groups[folderPosition]));
                // Console.WriteLine(folderListExpression.Matches(message)[folderListExpression.GroupNumberFromName("PackageSize")].Groups.Count);
                foreach (Group g in m.Groups)
                {
                    foreach (Capture c in g.Captures)
                    {
                        Console.WriteLine(string.Format("Capture:  {0}", c.Value));
                    }

                    Console.WriteLine(g.Value);
                }
            }

            foreach (string line in message.Split('\n'))
            {
                Console.WriteLine(line);
            }

            return new List<PackageFolder>();
        }

        /// <summary>
        /// Attempt to figure out where the dtutil.exe program is located by checking the
        /// registry.
        /// </summary>
        /// <returns>The fully qualified path to the SQL Server installation</returns>
        private string GetSQLServerDirectory()
        {
            string path = string.Empty;
            RegistryKey sqlServerKey = null;
            sqlServerKey = Registry.LocalMachine;

            // HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\DTS\Setup 
            sqlServerKey = sqlServerKey.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server\90\DTS\Setup");
            path = sqlServerKey.GetValue("SQLPath").ToString();

            return path;
        }

        /// <summary>
        /// An alternative measure for getting information about packages and folders by
        /// querying SQL Server directly.  Not sure if we'd ever go that route but it'd be
        /// nice to be able to bypass dtutil and only interact with SQL Server methods and objects.
        /// I suppose if I fired up profiler while dtutil ran, I might get an idea of how it works...
        /// </summary>
        /// <returns>A datatable holding our results.  Currently not implemented</returns>
        private System.Data.DataTable QuerySqlServer()
        {
            string sql = @"WITH FOLDERS AS 
(
	SELECT
		coalesce(NullIf(F.foldername, ''), '/') AS foldername
	,	F.folderid
	,	F.parentfolderid
	,	coalesce(NullIf(F.foldername, ''), Null) AS parentfoldername
	FROM
		msdb.dbo.sysdtspackagefolders90 F
	WHERE
		F.parentfolderid IS NULL	
	UNION ALL
	SELECT
		F.foldername
	,	F.folderid
	,	F.parentfolderid
	,	S.foldername
	FROM
		msdb.dbo.sysdtspackagefolders90 F
		INNER JOIN FOLDERS S
		ON S.folderid = F. parentfolderid
)
, PACKAGES AS
(
	SELECT 
		name as packagename
	,	folderid
	,	id
	FROM 
		msdb.dbo.sysdtspackages90
)
SELECT
	F.parentFolderName
,	F.foldername
,	P.packageName
FROM
	FOLDERS F
	LEFT OUTER JOIN
		PACKAGES P
		ON P.folderid = F.folderid

";
            return new System.Data.DataTable();
        }
 
        /// <summary>
        /// Get the last folder listed
        /// </summary>
        /// <param name="packagePath">A fully qualifed SQL Server path</param>
        /// <returns>The current folder</returns>
        private string GetCurrentFolder(string packagePath)
        {
            string currentFolder = string.Empty;
            int lastSeparator = 0;
            lastSeparator = packagePath.LastIndexOf("\\") + 1;
            currentFolder = packagePath.Substring(lastSeparator, packagePath.Length - lastSeparator);

            return currentFolder;
        }

        /// <summary>
        /// Given a path, return the enclosing folder.
        /// </summary>
        /// <param name="packagePath">A fully qualified SQL Server path</param>
        /// <returns>A fully qualified SQL Server path one level above what was sourced</returns>
        private string GetParentFolder(string packagePath)
        {
            string parentPath = string.Empty;
            string rootPath = string.Empty;
            System.IO.DirectoryInfo parentDirectory = null;
            parentPath = "\\";
            rootPath = @"\\";

            try
            {
                if (packagePath != rootPath)
                {
                    parentDirectory = System.IO.Directory.GetParent(packagePath);

                    if (parentDirectory != null)
                    {
                        parentPath = parentDirectory.ToString().Replace(System.IO.Directory.GetDirectoryRoot(parentDirectory.ToString()), parentPath);
                    }

                    if (parentPath == "\\")
                    {
                        parentPath = rootPath;
                    }
                }
            }
            catch (ArgumentException exception)
            {
                // chose to do nothing with it.  Shouldn't be an issue as we 
                // test above, but UNCs have not been tested
            }

            return parentPath;
        }
    }
}
